package com.parq.cs.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Vector;


public class CSDBProxy {

	private Connection connection;
	private PreparedStatement [] stmts;

	public CSDBProxy()throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		connection=DriverManager.getConnection(DBConfig.WEB_SEARCH_DB_URL,DBConfig.WEB_SEARCH_DB_USERNAME,DBConfig.WEB_SEARCH_DB_PASSWORD);
		connection.createStatement().executeUpdate("set names 'utf8'");
		stmts=new PreparedStatement[6];
		stmts[0]=connection.prepareStatement("insert into search (query,searchresults,searchDate) values (?,?,now());",Statement.RETURN_GENERATED_KEYS);
		stmts[1]=connection.prepareStatement("select searchid from search where query=?;");
		stmts[2]=connection.prepareStatement("insert into searchresults (searchid,searchdate,sentence,url) values (?,now(),?,?);");
		stmts[3]=connection.prepareStatement("select s.searchid,s.searchresults from searchtasks t inner join search s on s.searchid=t.searchid where t.completed is null;");
		stmts[4]=connection.prepareStatement("select searchresults from search where searchid=?;");
		stmts[5]=connection.prepareStatement("select searchid,searchdate,sentence,url from searchresults where url=?");
	}
	
	
	public ResultSet getCrawlTasks()throws Exception{
		return stmts[3].executeQuery();
	}
	
	public void addSearchResultContents(long searchid, Vector<String> sentences,String url)throws Exception{
		Iterator<String> itr=sentences.iterator();
		while (itr.hasNext()){
		String sentence=itr.next();
		stmts[2].clearParameters();
		stmts[2].setLong(1, searchid);
		stmts[2].setString(2, sentence);
		stmts[2].setString(3,url);
		stmts[2].executeUpdate();
		}
	}
	
	public ResultSet getCrawlResults(String url)throws Exception{
		stmts[5].clearParameters();
		stmts[5].setString(1, url);
		return stmts[5].executeQuery();
	}
	
	public long searchDB(String query)throws Exception{
		stmts[1].clearParameters();
		setString(stmts[1], 1, query);
		ResultSet rset=stmts[1].executeQuery();
		long id=0;
		if (rset.next()){
			id=rset.getLong("searchid");
		}
		return id;
	}

	public String getResultsFeed(long searchid)throws Exception{
		stmts[4].clearParameters();
		stmts[4].setLong(1, searchid);
		ResultSet rset=stmts[4].executeQuery();
		String res=null;
		if (rset.next()){
			res=rset.getString("searchresults");
		}
		
		if (res!=null){
			byte[] utf8Bytes = res.getBytes("UTF8");
			res= new String(utf8Bytes, "UTF8");
		}

		return res;
	}

	public long addToDB(String query,String resultsFeed)throws Exception{
		long id=0;
		stmts[0].clearParameters();
		setString(stmts[0], 1, query);
		setString(stmts[0], 2, resultsFeed);
		stmts[0].executeUpdate();
		ResultSet rset=stmts[0].getGeneratedKeys();
		if (rset.next()){
			id=rset.getLong(1);
		}
		
		return id;
	}
	
	public void close()throws Exception{
		this.connection.close();
	}
	
	public static void setString(PreparedStatement stmt,int index,String str)throws Exception{
		byte[] utf8Bytes = str.getBytes("UTF8");
	    str = new String(utf8Bytes, "UTF8");
	    str=str.replaceAll("'", "''");
	    stmt.setString(index, str);
	}

}
